package com.google.code.ptrends.services.implementations.dataservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.google.code.ptrends.entities.ItemRecord;

public class SaverServiceImpl extends DataServiceBaseImpl {

	private static final Logger LOG = Logger.getLogger(SaverServiceImpl.class);
	private final SimpleDateFormat dateformat = new SimpleDateFormat(
			"yyyy-MM-dd hh:mm:ss");

	protected SaverServiceImpl(Connection connection) {
		super(connection);
	}

	/**
	 * Saves new category to categories table
	 * 
	 * @param name
	 *            name of new category
	 * @return id of new category in the table
	 * @throws SQLException
	 */
	public int saveCategory(final String name) throws SQLException {
		if (StringUtils.isBlank(name)) {
			throw new IllegalArgumentException(
					"Illegal null or blank category name");
		}

		int result = -1;
		final String sqlText = "INSERT INTO categories (name) VALUES (?)";

		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sqlText,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, name);
			preparedStatement.executeUpdate();
			result = getGeneratedId(preparedStatement);
		} catch (SQLException e) {
			LOG.error("Problem with Category inserting", e);
			throw e;
		} finally {
			closeStatement(preparedStatement);
		}

		return result;
	}

	public int saveSupplier(final String name) throws SQLException {
		if (StringUtils.isBlank(name)) {
			throw new IllegalArgumentException(
					"Illegal null or blank supplier name");
		}

		int result = -1;
		final String sqlText = "INSERT INTO suppliers (name) VALUES (?)";

		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sqlText,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, name);
			preparedStatement.executeUpdate();
			result = getGeneratedId(preparedStatement);
		} catch (SQLException e) {
			LOG.error("Problem with Category inserting", e);
			throw e;
		} finally {
			closeStatement(preparedStatement);
		}

		return result;
	}

	/**
	 * Saves new class to classes table
	 * 
	 * @param name
	 *            name of new class
	 * @return id of new class in the table
	 */
	public int saveClass(final String name) throws SQLException {

		if (StringUtils.isBlank(name)) {
			throw new IllegalArgumentException("Illegal null saveClass name");
		}

		int result = -1;
		final String sqlText = "INSERT INTO classes (name) VALUES (?)";

		PreparedStatement preparedStatement = null;
		SQLException error = null;

		try {
			preparedStatement = connection.prepareStatement(sqlText,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, name);
			preparedStatement.executeUpdate();
			result = getGeneratedId(preparedStatement);
		} catch (SQLException e) {
			LOG.error("Problem with Class inserting", e);
			error = e;
		} finally {
			closeStatement(preparedStatement);
		}

		if (error != null) {
			throw error;
		}

		return result;
	}

	public int saveItem(final ItemRecord item) throws SQLException {

		if (item == null) {
			throw new IllegalArgumentException(
					"Illegal null-reference saveItem ItemRecord");
		}

		int result = -1;

		final String sqlText = "INSERT INTO items (model, category_id, manufacturer_id, class_id, version_date) "
				+ "VALUES (?, ?, ?, ?, ?)";

		PreparedStatement preparedStatement = null;
		SQLException error = null;

		try {
			preparedStatement = connection.prepareStatement(sqlText,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, item.getModel());
			preparedStatement.setInt(2, item.getCategoryID());
			preparedStatement.setInt(3, item.getManufacturerID());
			preparedStatement.setInt(4, item.getClassID());
			preparedStatement.setString(5,
					dateformat.format(Calendar.getInstance().getTime()));
			preparedStatement.executeUpdate();

			result = getGeneratedId(preparedStatement);
		} catch (SQLException e) {
			LOG.error("Problem with Item inserting", e);
			error = e;
		} finally {
			closeStatement(preparedStatement);
		}

		if (error != null) {
			throw error;
		}

		return result;
	}

	/**
	 * Saves new manufacturer to manufacturers table in database
	 * 
	 * @param name
	 *            manufacturer`s name
	 * @param webSite
	 *            manufacturer`s webSite
	 * @return id of new manufacturer in the table
	 * @throws SQLException
	 */
	public int saveManufacturer(final String name, final String webSite)
			throws SQLException {

		if (StringUtils.isBlank(name)) {
			throw new IllegalArgumentException(
					"Illegal null saveManufacturer name");
		}

		int result = -1;

		final String sqlText = "insert into MANUFACTURERS (name, website) values (?, ?)";
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sqlText,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, name);

			preparedStatement.setString(2,
					StringUtils.isBlank(webSite) == true ? "http://foo.com"
							: webSite);
			preparedStatement.executeUpdate();
			result = getGeneratedId(preparedStatement);
		} catch (SQLException e) {
			LOG.error("Problem with Manufacturer inserting", e);
			throw e;
		} finally {
			closeStatement(preparedStatement);
		}

		return result;
	}
}
